const router = require("koa-router")();
const knex = require("../config/knex");
const userRouter = require("./user");
const questions = require("../data/question");
const setResponse = require("../utils/api");
const {
  sortformTypes,
  filterQuestionwithEmptyValue,
} = require("../utils/filter");

knex
  .raw("SELECT 1")
  .then(() => console.log("Connection successful"))
  .catch((err) => console.error("Connection failed", err));

  // knex('users').insert({
  //   phone: "18379996996",
  //   nickname: "最牛逼",
  //   password: "123456",
  //   roleType: 2,
  //   avatarUrl: "",
  // }).then(() => {
  //   console.log('Data inserted successfully');
  // }).catch((err) => {
  //   console.log('Error inserting data:', err);
  // });

router.get("/", async (ctx) => {
  ctx.body = "Hello World";
});

// 获取数据操作
function getTableData(name) {
  router.post(`/${name}`, async (ctx) => {
    const {
      page,
      size,
      tag = "",
      paperName = "",
      categoryName = "",
      examName = "",
      title = "",
      question = "",
      ...params
    } = ctx.request.body;
    await knex.raw("SELECT 1");
    let req = knex(name).where(params);
    if (tag) {
      req = req.where("tags", "like", `%${tag}%`);
    }
    if (paperName) {
      req = req.where("paperName", "like", `%${paperName}%`);
    }

    if (categoryName) {
      req = req.where("categoryName", "like", `%${categoryName}%`);
    }
    if (examName) {
      req = req.where("examName", "like", `%${examName}%`);
    }
    if (title) {
      req = req.where("title", "like", `%${title}%`);
    }
    if (question) {
      req = req.where("question", "like", `%${question}%`);
    }
    const data = await req
      .orderBy("updated_at", "desc")
      .orderBy("created_at", "desc")
      .select("*");
    let result = [];
    if (page && size) {
      result = data.slice((page - 1) * size, page * size);
    } else {
      result = data;
    }

    setResponse(ctx, result, data.length);
  });
}

// 更新单个数据
function updateTableData(path, name) {
  router.post(path, async (ctx) => {
    const { _id, ...params } = ctx.request.body;
    const data = await knex(name)
      .where({ _id })
      .update({ ...params, updated_at: knex.fn.now() });
    setResponse(ctx, data);
  });
}

// 上传表格操作
function uploadTableData(path, name) {
  router.post(path, async (ctx) => {
    const { rows } = ctx.request.body; // rows为表格上传的数据

    // 获取数据库中所有的标签数据，并将其转换为一个以 _id 为键，以标签对象为值的对象。
    const targetData = await knex(name).select("*");
    const total = targetData.length;
    // 将标签数据转换为对象
    const targetMap = {};
    targetData.forEach((it) => {
      targetMap[it._id] = it;
    });

    // 遍历表格上传的数据
    const updatedIds = new Set(); // 存储需要更新的标签的 _id
    const newTargetData = []; // 存储需要插入的标签
    const header = rows[0].slice(1, -2);
    rows.slice(1).forEach((row, idx) => {
      const id = row[0];
      const tag = targetMap[id];
      if (tag) {
        updatedIds.add(String(id));
        const res = row.slice(1);
        const updatedTag = {};
        let flag = false;
        header.forEach((it, index) => {
          updatedTag[it] = res[index];
          if (tag[it] !== res[index]) flag = true;
        });
        // 如果 _id 在数据库中存在，则将该行数据与数据库中的数据进行比较，如果有不同，则更新数据库中的数据。
        if (flag) {
          updatedTag["updated_at"] = knex.fn.now();
          knex(name).where({ _id: id }).update(updatedTag).catch(console.error);
        }
      } else {
        // 如果 _id 在数据库中不存在，则将该行数据插入到数据库中。
        const res = row.slice(1);
        const addData = {};
        addData["_id"] = total + idx + 1;
        header.forEach((it, index) => {
          addData[it] = res[index];
        });
        newTargetData.push(addData);
      }
    });

    // 将新的标签插入到数据库中
    if (newTargetData.length > 0) {
      knex(name).insert(newTargetData).catch(console.error);
    }

    // 遍历数据库中的标签数据，将不存在于表格上传的数据中的标签从数据库中删除。
    const deleteIds = Object.keys(targetMap).filter(
      (id) => !updatedIds.has(id)
    );
    if (deleteIds.length > 0) {
      knex("tags").whereIn("_id", deleteIds).del().catch(console.error);
    }

    setResponse(ctx);
  });
}

function deleteTableData(path, name) {
  router.post(path, async (ctx) => {
    const { rows } = ctx.request.body;
    const res = await knex(name).whereIn("_id", rows).del();
    setResponse(ctx, res);
  });
}

// 考试
getTableData("exams");
deleteTableData("/deteleExams", "exams");
updateTableData("/updateExams", "exams");
uploadTableData("/uploadExams", "exams");
router.post("/addExams", async (ctx) => {
  const res = await knex("exams").insert(ctx.request.body);
  setResponse(ctx, res);
});
// 获取个人考试
router.post("/getPersonalExams", async (ctx) => {
  const { userID } = ctx.request.body;
  const data = await knex("exams").select("*");
  const res = data // 过滤参与考生
    .filter(({ userIDs }) => userIDs && JSON.parse(userIDs).includes(userID))
    .map(({ userIDs, ...rest }) => rest);
  setResponse(ctx, res);
});

// 答题详情
getTableData("answer_details")
deleteTableData("/delete_answer_details", "answer_details")

// 标签
getTableData("tags");
deleteTableData("/deleteTags", "tags");
updateTableData("/updateTags", "tags");
uploadTableData("/uploadTags", "tags");
router.post("/addTags", async (ctx) => {
  const { tagsName } = ctx.request.body;
  const res = await knex("tags").insert(
    tagsName.map((it) => ({ tagName: it }))
  );
  setResponse(ctx, res);
});

// 分类
getTableData("categories");
deleteTableData("/deleteCategories", "categories");
updateTableData("/updateCategories", "categories");
uploadTableData("/uploadCategories", "categories");
router.post("/addCategories", async (ctx) => {
  const { categoriesName } = ctx.request.body;
  const res = await knex("categories").insert(
    categoriesName.map((it) => ({ categoryName: it }))
  );
  setResponse(ctx, res);
});

// 评论
getTableData("comments");
deleteTableData("/deleteComment", "comments");
updateTableData("/updateComment", "comments");
router.post("/addComment", async (ctx) => {
  const res = await knex("comments").insert(ctx.request.body);
  setResponse(ctx, res);
});

// 帖子
getTableData("forums");
deleteTableData("/deleteForums", "forums");
updateTableData("/updateForums", "forums");
router.post("/addForums", async (ctx) => {
  const res = await knex("forums").insert(ctx.request.body);
  setResponse(ctx, res);
});

// 文章管理
getTableData("posts");
deleteTableData("/deletePosts", "posts");
updateTableData("/updatePosts", "posts");
uploadTableData("/uploadPosts", "posts");
router.post("/addPosts", async (ctx) => {
  const res = await knex("posts").insert(ctx.request.body);
  setResponse(ctx, res);
});

getTableData("questions");
deleteTableData("/delQuestion", "questions");
uploadTableData("/uploadQuestions", "questions");
updateTableData("/updateQuestions", "questions");
// 随机生成题目
router.post("/randomQuestions", async (ctx) => {
  const {
    number = 10,
    tag = "",
    ...params
  } = filterQuestionwithEmptyValue(ctx.request.body);
  console.log(filterQuestionwithEmptyValue(params), number);
  const data = await knex("questions")
    .orderByRaw("RAND()")
    .limit(number)
    .where("tags", "like", `%${tag}%`)
    .where(params)
    .select("*");
  setResponse(ctx, sortformTypes(data), "获取题目成功");
});

// 试卷
getTableData("papers");
deleteTableData("/delPaper", "papers");
updateTableData("/updatePaper", "papers");
router.post("/addPaper", async (ctx) => {
  const res = await knex("papers").insert(ctx.request.body);
  setResponse(ctx, res);
});
router.post("/updatePaperStatus", async (ctx) => {
  const { _id, isOpen } = ctx.request.body;
  const res = await knex("papers").where({ _id }).update({ isOpen });
  setResponse(ctx, res);
});
router.post("/getNoAnswerPaper", async (ctx) => {
  const { paperID } = ctx.request.body;
  const res = await knex("papers").select("*");
  let data = null;
  if (res.length > 0) {
    data = JSON.parse(res[0].paperContent).map(
      ({ question, options, description,type, ...rest }) => ({
        question,
        options,
        description,
        type,
      })
    );
  }
  setResponse(ctx, data);
});
// router.post("/getPaperContent", async (ctx) => {
//   const { ids } = ctx.request.body;
//   const data = await knex("questions").whereIn('_id', ids).select("*")
//   setResponse(ctx, sortformTypes(data));
// });

// 3. 表格上传标签

// 用户相关
router.use("/api/user", userRouter.routes());

router.post("/upload", async (ctx) => {
  const { file } = ctx.request.files;

  ctx.body = {
    code: 200,
    message: "上传成功",
    data: {
      url: `/${file.newFilename}`,
    },
  };
});

// 答题相关
// 1. 获取所有答题
router.get("/answers", async (ctx) => {
  const answers = await knex("answers").select("*");
  ctx.body = answers;
});

// 2. 添加一个答题
router.post("/addAnswer", async (ctx) => {
  const { questionId, userSelectAnswer } = ctx.request.body;
  const answers = await knex("answers").insert({
    questionId,
    userSelectAnswer,
  });
  ctx.body = answers;
});

// 3. 删除一个答题
router.delete("/deleteAnswerById", async (ctx) => {
  const { _id } = ctx.request.body;
  const answers = await knex("answers").where({ _id }).del();
  ctx.body = answers;
});

// 4. 批量删除答题
router.post("/deleteAnswers", async (ctx) => {
  const { _id, rows } = ctx.request.body;
  const answers = await knex("answers").where({ _id }).del(rows);
  ctx.body = answers;
});

// 2. 添加一个题目
router.post("/addQuestion", async (ctx) => {
  const questions = await knex("questions").insert(ctx.request.body);
  setResponse(ctx, questions);
});

// 3. 删除一个题目
router.delete("/deleteQuestionById", async (ctx) => {
  const { _id } = ctx.request.body;
  const questions = await knex("questions").where({ _id }).del();
  ctx.body = questions;
});

// 4. 批量删除题目
router.post("/deleteQuestions", async (ctx) => {
  const { _id, rows } = ctx.request.body;
  const questions = await knex("questions").where({ _id }).del(rows);
  ctx.body = questions;
});

// 5. 编辑一个题目
router.post("/editQuestion", async (ctx) => {
  const { _id, title, content } = ctx.request.body;
  const questions = await knex("questions")
    .where({ _id })
    .update({ title, content });
  ctx.body = questions;
});

// 6. 批量编辑题目
router.post("/editQuestions", async (ctx) => {
  const { _id, rows } = ctx.request.body;
  const questions = await knex("questions").where({ _id }).update(rows);
  ctx.body = questions;
});

// 选项相关
// 1. 获取所有选项
router.get("/options", async (ctx) => {
  const options = await knex("options").select("*");
  ctx.body = options;
});

// 2. 添加一个选项
router.post("/addOption", async (ctx) => {
  const { title, content } = ctx.request.body;
  const options = await knex("options").insert({ title, content });
  ctx.body = options;
});

// 3. 删除一个选项
router.delete("/deleteOptionById", async (ctx) => {
  const { _id } = ctx.request.body;
  const options = await knex("options").where({ _id }).del();
  ctx.body = options;
});

// 4. 批量删除选项
router.post("/deleteOptions", async (ctx) => {
  const { _id, rows } = ctx.request.body;
  const options = await knex("options").where({ _id }).del(rows);
  ctx.body = options;
});

// 分数相关
// 1. 获取所有分数
router.get("/scores", async (ctx) => {
  const scores = await knex("scores").select("*");
  ctx.body = scores;
});

// 2. 添加一个分数
router.post("/addScore", async (ctx) => {
  const { title, content } = ctx.request.body;
  const scores = await knex("scores").insert({ title, content });
  ctx.body = scores;
});

// 3. 删除一个分数
router.delete("/deleteScoreById", async (ctx) => {
  const { _id } = ctx.request.body;
  const scores = await knex("scores").where({ _id }).del();
  ctx.body = scores;
});

// 4. 批量删除分数
router.post("/deleteScores", async (ctx) => {
  const { _id, rows } = ctx.request.body;
  const scores = await knex("scores").where({ _id }).del(rows);
  ctx.body = scores;
});

// 分类相关
// 1. 获取所有分类
router.get("/categories", async (ctx) => {
  const categories = await knex("categories").select("*");
  ctx.body = categories;
});

// 2. 添加一个分类
router.post("/addCategory", async (ctx) => {
  const { title, content } = ctx.request.body;
  const categories = await knex("categories").insert({ title, content });
  ctx.body = categories;
});

// 3. 删除一个分类
router.delete("/deleteCategoryById", async (ctx) => {
  const { _id } = ctx.request.body;
  const categories = await knex("categories").where({ _id }).del();
  ctx.body = categories;
});

// 4. 批量删除分类
router.post("/deleteCategories", async (ctx) => {
  const { _id, rows } = ctx.request.body;
  const categories = await knex("categories").where({ _id }).del(rows);
  ctx.body = categories;
});

// 5. 编辑一个分类
router.post("/editCategory", async (ctx) => {
  const { _id, title, content } = ctx.request.body;
  const categories = await knex("categories")
    .where({ _id })
    .update({ title, content });
  ctx.body = categories;
});

// 6. 批量编辑分类
router.post("/editCategories", async (ctx) => {
  const { _id, rows } = ctx.request.body;
  const categories = await knex("categories").where({ _id }).update(rows);
  ctx.body = categories;
});

// 2. 添加一个标签

// 3. 批量删除标签

// 4. 编辑标签

// 5. 批量编辑标签
router.post("/editTags", async (ctx) => {
  const { _id, rows } = ctx.request.body;
  const tags = await knex("tags").where({ _id }).update(rows);
  ctx.body = tags;
});

router.get("/exams", async (ctx) => {
  ctx.body = [
    {
      id: 1,
      name: "笔试1",
      date: "2023-05-20",
      time: "09:00:00",
      duration: 120,
      location: "教学楼101",
    },
    {
      id: 2,
      name: "笔试2",
      date: "2023-03-20",
      time: "14:00:00",
      duration: 90,
      location: "教学楼201",
    },
  ];
});
router.get("/students", async (ctx) => {
  ctx.body = [
    {
      id: 1,
      name: "张三",
      gender: "男",
      age: 20,
      contact: "13812345678",
    },
    {
      id: 2,
      name: "李四",
      gender: "女",
      age: 22,
      contact: "13987654321",
    },
  ];
});
router.get("/report", async (ctx) => {
  ctx.body = [
    { name: "张三", score: 80, pass: true },
    { name: "李四", score: 60, pass: false },
    { name: "王五", score: 90, pass: true },
    { name: "赵六", score: 70, pass: true },
    { name: "钱七", score: 50, pass: false },
    { name: "孙八", score: 85, pass: true },
    { name: "周九", score: 75, pass: true },
    { name: "吴十", score: 55, pass: false },
  ];
});

const postCountData = [
  { label: "1月", count: 100 },
  { label: "2月", count: 200 },
  { label: "3月", count: 300 },
  { label: "4月", count: 400 },
  { label: "5月", count: 500 },
  { label: "6月", count: 600 },
];

const userCountData = [
  { label: "1月", count: 50 },
  { label: "2月", count: 100 },
  { label: "3月", count: 150 },
  { label: "4月", count: 200 },
  { label: "5月", count: 250 },
  { label: "6月", count: 300 },
];

const trafficData = [
  { label: "1月", traffic: 1000 },
  { label: "2月", traffic: 2000 },
  { label: "3月", traffic: 3000 },
  { label: "4月", traffic: 4000 },
  { label: "5月", traffic: 5000 },
  { label: "6月", traffic: 6000 },
];

const activityData = [
  { label: "1月", activity: 50 },
  { label: "2月", activity: 100 },
  { label: "3月", activity: 150 },
  { label: "4月", activity: 200 },
  { label: "5月", activity: 250 },
  { label: "6月", activity: 300 },
];

// API路由
router.get("/post-count", (ctx) => {
  ctx.body = postCountData;
});

router.get("/user-count", (ctx) => {
  ctx.body = userCountData;
});

router.get("/traffic", (ctx) => {
  ctx.body = trafficData;
});

router.get("/activity", (ctx) => {
  ctx.body = activityData;
});

module.exports = router;
